package eoeProject.edenOfEast.utils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import eoeProject.edenOfEast.bean.EOEAnalysesBean;
import eoeProject.edenOfEast.bean.EOEAnalysesGJBean;
import eoeProject.edenOfEast.bean.EOEUserBean;
import eoeProject.edenOfEast.core.DBConnector;
import eoeProject.edenOfEast.core.DBUtils;
import eoeProject.edenOfEast.core.SQLMaker;
import eoeProject.edenOfEast.dataBase.DB_EOE_ANALYSES;
import eoeProject.edenOfEast.dataBase.DB_EOE_ANALYSES_CATEGORY;
import eoeProject.edenOfEast.dataBase.DB_EOE_ANALYSES_GJ;
import eoeProject.edenOfEast.dataBase.DB_EOE_ANALYSES_RES;

public class AnalysesUtils {

	public static String getCategoryName(Integer id) {
		String value = "";
		SQLMaker sql = new SQLMaker(DB_EOE_ANALYSES_CATEGORY.TABLE_NAME,
				SQLMaker.SQL_TYPE_SELECT);
		sql.addExpEquals(DB_EOE_ANALYSES_CATEGORY.CATEGORY_ID, id);
		ResultSet rs = DBConnector.getInstance().get(sql.getSQL());
		try {
			if (rs.next()) {
				value = rs.getString(DB_EOE_ANALYSES_CATEGORY.NAME);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO 自動生成された catch ブロック
			System.out.println("AnalysesUtilException: " + e.getMessage());
			return null;
		}
		return value;
	}

	public static Integer getCategoryId(String name) {
		Integer value = 0;
		SQLMaker sql = new SQLMaker(DB_EOE_ANALYSES_CATEGORY.TABLE_NAME,
				SQLMaker.SQL_TYPE_SELECT);
		sql.addExpEquals(DB_EOE_ANALYSES_CATEGORY.NAME, name);
		ResultSet rs = DBConnector.getInstance().get(sql.getSQL());
		try {
			if (rs.next()) {
				value = rs.getInt(DB_EOE_ANALYSES_CATEGORY.CATEGORY_ID);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO 自動生成された catch ブロック
			System.out.println("AnalysesUtilException: " + e.getMessage());
			return null;
		}
		return value;
	}

	public static EOEAnalysesBean getAnalysesBean(Integer id) {
		EOEAnalysesBean analyses = new EOEAnalysesBean();

		SQLMaker sql = new SQLMaker(DB_EOE_ANALYSES.TABLE_NAME,
				SQLMaker.SQL_TYPE_SELECT);
		sql.addExpEquals(DB_EOE_ANALYSES.ANALYSES_ID, id);
		ResultSet rs = DBConnector.getInstance().get(sql.getSQL());
		try {
			if (rs.next()) {
				analyses.setAnalysesId(rs.getInt(DB_EOE_ANALYSES.ANALYSES_ID));
				analyses.setCategoryId((rs.getInt(DB_EOE_ANALYSES.CATEGORY_ID)));

				analyses.setCreateUserId((rs
						.getInt(DB_EOE_ANALYSES.CREATE_USER_ID)));
				analyses.setTitle(rs.getString(DB_EOE_ANALYSES.TITLE));
				analyses.setCreateDate(rs
						.getTimestamp(DB_EOE_ANALYSES.CREATE_DATE));
				analyses.setUpdateDate(rs
						.getTimestamp(DB_EOE_ANALYSES.UPDATE_DATE));
				analyses.setStatus(rs.getInt(DB_EOE_ANALYSES.STATUS));
				analyses.setAbout(rs.getString(DB_EOE_ANALYSES.ABOUT));
				analyses.setCreateUserName(rs
						.getString(DB_EOE_ANALYSES.CREATE_USER_NAME));
				analyses.setTag1(rs.getString(DB_EOE_ANALYSES.TAG1));
				analyses.setTag2(rs.getString(DB_EOE_ANALYSES.TAG2));
				analyses.setTag3(rs.getString(DB_EOE_ANALYSES.TAG3));
				analyses.setTotalRes(rs.getInt(DB_EOE_ANALYSES.TOTAL_RES));
				analyses.setCategoryName(getCategoryName(analyses
						.getCategoryId()));
			}
			rs.close();
		} catch (SQLException e) {
			// TODO 自動生成された catch ブロック
			System.out.println("AnalysesUtilException: " + e.getMessage());
			return null;
		}
		return analyses;
	}

	public static Integer getNextResNum(Integer analysesId) {
		Integer value = 0;

		SQLMaker sql = new SQLMaker(DB_EOE_ANALYSES_RES.TABLE_NAME,
				SQLMaker.SQL_TYPE_SELECT);
		sql.addExpEquals(DB_EOE_ANALYSES_RES.ANALYSES_ID, analysesId);
		sql.addOrderBy(DB_EOE_ANALYSES_RES.RES_NUM, "desc");
		sql.addLimit(1);
		ResultSet rs = DBConnector.getInstance().get(sql.getSQL());
		try {
			if (rs.next()) {
				value = rs.getInt(DB_EOE_ANALYSES_RES.RES_NUM) + 1;
			}
			rs.close();
		} catch (SQLException e) {
			// TODO 自動生成された catch ブロック
			System.out.println("AnalysesUtilException: " + e.getMessage());
			return null;
		}
		return value;
	}

	public static List<String> getCategoryList() {
		List<String> list = new ArrayList<String>();
		SQLMaker sql = new SQLMaker(DB_EOE_ANALYSES_CATEGORY.TABLE_NAME,
				SQLMaker.SQL_TYPE_SELECT);
		ResultSet rs = DBConnector.getInstance().get(sql.getSQL());
		try {
			while (rs.next()) {
				list.add(rs.getString(DB_EOE_ANALYSES_CATEGORY.NAME));
			}

			rs.close();
		} catch (SQLException e) {
			// TODO 自動生成された catch ブロック
			System.out.println("AnalysesUtilException: " + e.getMessage());
		}
		return list;

	}

	public static List<EOEUserBean> getGJUserList(Integer resId) {
		List<EOEUserBean> beanList = new ArrayList<EOEUserBean>();
		List<Integer> idList = new ArrayList<Integer>();
		SQLMaker sql = new SQLMaker(DB_EOE_ANALYSES_GJ.TABLE_NAME,
				SQLMaker.SQL_TYPE_SELECT);
		sql.addExpEquals(DB_EOE_ANALYSES_GJ.RES_ID, resId);
		ResultSet rs = DBConnector.getInstance().get(sql.getSQL());
		try {
			while (rs.next()) {
				idList.add(rs.getInt(DB_EOE_ANALYSES_GJ.USER_ID));
			}
			rs.close();
		} catch (SQLException e) {
			// TODO 自動生成された catch ブロック
			System.out.println("AnalysesUtilException: " + e.getMessage());
		}

		for (int i : idList) {
			beanList.add(EOEUtils.getUserBean(i));
		}

		return beanList;
	}

	public static boolean doGJ(Integer resId, Integer userId, Integer gjCount) {
		SQLMaker sql = new SQLMaker(DB_EOE_ANALYSES_GJ.TABLE_NAME,
				SQLMaker.SQL_TYPE_INSERT);
		EOEAnalysesGJBean bean = new EOEAnalysesGJBean();
		bean.setGJId(DBUtils.getNextId(DB_EOE_ANALYSES_GJ.TABLE_NAME,
				DB_EOE_ANALYSES_GJ.PRIMARY_KEY));
		bean.setResId(resId);
		bean.setUserId(userId);
		bean.setCreateDate(new Date());
		bean.setUpdateDate(new Date());
		sql.setInsert(bean);
		if (DBConnector.getInstance().set(sql.getSQL())) {
			return false;
		}

		SQLMaker sql2 = new SQLMaker(DB_EOE_ANALYSES_RES.TABLE_NAME,
				SQLMaker.SQL_TYPE_UPDATE);
		sql2.addSetValue(DB_EOE_ANALYSES_RES.GJ, gjCount + 1);
		sql2.addSetValue(DB_EOE_ANALYSES_RES.UPDATE_DATE, new Date());
		sql2.addExpEquals(DB_EOE_ANALYSES_RES.PRIMARY_KEY, resId);
		if (DBConnector.getInstance().set(sql2.getSQL())) {
			return false;
		}
		return true;
	}

	public static boolean deleteGJ(Integer gjId) {
		SQLMaker sql = new SQLMaker(DB_EOE_ANALYSES_GJ.TABLE_NAME,
				SQLMaker.SQL_TYPE_DELETE);
		sql.addExpEquals(DB_EOE_ANALYSES_GJ.GJ_ID, gjId);
		if (DBConnector.getInstance().set(sql.getSQL())) {
			return false;
		}

		return true;
	}

	public static Integer getGJId(Integer resId, Integer userId) {
		Integer value = 0;
		SQLMaker sql = new SQLMaker(DB_EOE_ANALYSES_GJ.TABLE_NAME,
				SQLMaker.SQL_TYPE_SELECT);
		sql.addExpEquals(DB_EOE_ANALYSES_GJ.RES_ID, resId);
		sql.addExpEquals(DB_EOE_ANALYSES_GJ.USER_ID, userId);
		ResultSet rs = DBConnector.getInstance().get(sql.getSQL());
		try {
			while (rs.next()) {
				value = rs.getInt(DB_EOE_ANALYSES_GJ.GJ_ID);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO 自動生成された catch ブロック
			System.out.println("AnalysesUtilException: " + e.getMessage());
		}
		return value;
	}

}
